import time, json, subprocess
from datetime import datetime, date
from typing import Union


def _jsonize_param(value):
    if isinstance(value, (list, dict)):
        return f"<json>{json.dumps(value)}</json>"
    return value

def _dejsonize_value(value):
    if isinstance(value, str):
        if value.startswith("<json>") and value.endswith("</json>"):
            json_str = value[6:-7]
            try:
                return json.loads(json_str)
            except json.JSONDecodeError:
                return value  # JSON 解析失败，原样返回
    return value

# -----------------------------------------------------
class sql_command:

    def __init__(self, db_type):
        """
        初始化 SqlFun 类，传入数据库类型。
        :param db_type: 数据库类型，支持 'sqlite'、'mysql'
        """
        if db_type not in ['sqlite', 'mysql', 'ccport']:
            raise ValueError("不支持的数据库类型")
        if db_type == 'ccport':
            db_type = 'sqlite'
        self.db_type = db_type

    def qt(self, identifier):  # quote
        if self.db_type == 'mysql':
            quote_char = '`'
        else:  # self.db_type == 'sqlite'
            quote_char = '"'  # 默认使用双引号
        identifier = str(identifier)
        if not identifier.startswith(quote_char) and not identifier.endswith(quote_char):
            return f'{quote_char}{identifier}{quote_char}'
        return identifier

    def ph(self):
        if self.db_type == 'mysql':
            return '%s'
        else:  # self.db_type == 'sqlite'
            return '?'

    def table_get(self):
        if self.db_type == 'sqlite':
            sql = "SELECT name FROM sqlite_master WHERE type='table'"
        else:  # self.db_type == 'mysql'
            sql = "SHOW TABLES"

        sql_list = [sql]

        return sql_list

    def table_add(self, table_name_list):
        sql_list = []

        for name in table_name_list:
            if self.db_type == 'sqlite':
                sql = f"CREATE TABLE IF NOT EXISTS {self.qt(name)} (id INTEGER PRIMARY KEY AUTOINCREMENT)"
            else:  # self.db_type == 'mysql'
                sql = f"CREATE TABLE IF NOT EXISTS {self.qt(name)} (id INT AUTO_INCREMENT PRIMARY KEY)"

            sql_list.append(sql)

        return sql_list

    def table_delete(self, table_name_list):
        sql_list = []

        for name in table_name_list:
            sql = f"DROP TABLE IF EXISTS {self.qt(name)}"

            sql_list.append(sql)

        return sql_list

    def table_rename(self, old_table_name, new_table_name):
        """
        生成修改表名的 SQL 语句，支持 SQLite 和 MySQL。
        :param old_table_name: 旧表名
        :param new_table_name: 新表名
        """
        sql = f"ALTER TABLE {self.qt(old_table_name)} RENAME TO {self.qt(new_table_name)}"
        return [sql]

    def column_get(self, table_name):
        if self.db_type == 'sqlite':
            sql = f"PRAGMA table_info({self.qt(table_name)})"
        else:  # self.db_type == 'mysql'
            sql = f"SHOW COLUMNS FROM {self.qt(table_name)}"

        sql_list = [sql]

        return sql_list

    def column_add(self, table_name, column_dict):
        """
        生成添加列的 SQL 语句（不包含 IF NOT EXISTS）。
        :param table_name: 表名
        :param column_dict: 列定义 需是字典
        """
        sql_list = []

        for column_name, data_type in column_dict.items():
            sql = f"ALTER TABLE {self.qt(table_name)} ADD COLUMN {self.qt(column_name)} {data_type}"
            sql_list.append(sql)

        return sql_list

    def column_delete(self, table_name, del_col_list, columns_exist_list):
        """
        生成删除列的 SQL 语句，支持 SQLite 和 MySQL。
        :param table_name: 表名
        :param del_col_list: 要删除的列名列表
        :param columns_exist_list: 现有的列名列表
        """
        remaining_columns = [col for col in columns_exist_list if col not in del_col_list]

        if self.db_type == 'sqlite':
            qt_remaining_columns = [self.qt(col) for col in remaining_columns]
            columns_str = ', '.join(qt_remaining_columns)
            temp_table_name = f"{table_name}_temp"

            # sql_list = [
            #     f"ALTER TABLE {self.qt(table_name)} RENAME TO {self.qt(temp_table_name)}",
            #     f"CREATE TABLE {self.qt(table_name)} AS SELECT {columns_str} FROM {self.qt(temp_table_name)}",
            #     f"DROP TABLE {self.qt(temp_table_name)}"
            # ]

            sql_list = [
                # 1️⃣ 重命名原表
                f"ALTER TABLE {self.qt(table_name)} RENAME TO {self.qt(temp_table_name)}",

                # 2️⃣ 创建新表，显式保留 id 自增
                f"""
                CREATE TABLE {self.qt(table_name)} (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    {', '.join(f'{self.qt(col)} TEXT' for col in remaining_columns if col != 'id')}
                )
                """.strip(),

                # 3️⃣ 复制数据（包括 id）
                f"INSERT INTO {self.qt(table_name)} ({columns_str}) SELECT {columns_str} FROM {self.qt(temp_table_name)}",

                # 4️⃣ 删除临时表
                f"DROP TABLE {self.qt(temp_table_name)}"
            ]


        else:  # self.db_type == 'mysql'
            sql_list = [f"ALTER TABLE {self.qt(table_name)} DROP COLUMN {self.qt(column_name)}" for column_name in del_col_list]

        return sql_list

    def column_rename(self, table_name, old_column_name, new_column_name):
        """
        生成修改列名的 SQL 语句，支持 SQLite 和 MySQL。
        :param table_name: 表名
        :param old_column_name: 旧列名
        :param new_column_name: 新列名
        """
        if self.db_type == 'sqlite':
            sql = f"ALTER TABLE {self.qt(table_name)} RENAME COLUMN {self.qt(old_column_name)} TO {self.qt(new_column_name)}"
        else:  # self.db_type == 'mysql'
            sql = f"ALTER TABLE {self.qt(table_name)} CHANGE {self.qt(old_column_name)} {self.qt(new_column_name)} TEXT"

        return [sql]

    def record_add(self, table_name, data_dict):
        # 排除 'id' 字段（假设 'id' 是自动递增的主键）
        data_dict = {k: v for k, v in data_dict.items() if k.lower() != 'id'}

        qt_columns = ', '.join(self.qt(col) for col in data_dict.keys())
        placeholders = ', '.join(self.ph() for _ in data_dict)
        values = tuple(data_dict.values())

        sql = f"INSERT INTO {self.qt(table_name)} ({qt_columns}) VALUES ({placeholders})"

        sql_list = [(sql, values)]

        return sql_list

    def record_update(self, table_name, data_dict, condition):
        qt_set_clause = ', '.join(f"{self.qt(key)} = {self.ph()}" for key in data_dict.keys())
        sql = f"UPDATE {self.qt(table_name)} SET {qt_set_clause} WHERE {condition}"
        values = tuple(data_dict.values())  # 将值转换为元组

        sql_list = [(sql, values)]

        return sql_list

    def record_update_dict(self, table_name, data_dict, condition_dict):
        # 生成 SET 子句
        qt_set_clause = ', '.join(f"{self.qt(key)} = {self.ph()}" for key in data_dict.keys())
        sql = f"UPDATE {self.qt(table_name)} SET {qt_set_clause}"

        # 如果有 condition_dict，生成 WHERE 子句
        if condition_dict:
            condition_list = [f"{self.qt(key)} = {self.ph()}" for key in condition_dict]
            condition_str = " AND ".join(condition_list)
            sql += f" WHERE {condition_str}"

        # 将 data_dict 和 condition_dict 中的值合并为一个参数列表
        values = tuple(data_dict.values()) + tuple(condition_dict.values())

        sql_list = [(sql, values)]

        return sql_list

    def record_delete(self, table_name, condition):
        """
        删除记录。condition 是查询条件。
        :param table_name: 表名
        :param condition: 查询条件字符串
        """
        sql = f"DELETE FROM {self.qt(table_name)} WHERE {condition}"

        sql_list = [sql]

        return sql_list

    def record_delete_dict(self, table_name, condition_dict):
        """
        删除记录。condition 是查询条件。
        :param table_name: 表名
        :param condition: 查询条件字符串
        """
        sql = f"DELETE FROM {self.qt(table_name)}"

        # 如果有 condition_dict，生成 WHERE 子句
        if condition_dict:
            condition_list = [f"{self.qt(key)} = {self.ph()}" for key in condition_dict]
            condition_str = " AND ".join(condition_list)
            sql += f" WHERE {condition_str}"

        values = tuple(condition_dict.values())

        sql_list = [(sql, values)]

        return sql_list

    def record_search(self, table_name, columns, condition, order_by, Page_Str):
        """
        :param table_name: 表名
        :param columns: 列名
        :param condition: 查询条件，可选
        :param order_by: 排序方式，可选
        :param Page_Str: 分页参数，默认 None, 已包含 LIMIT BY 等关键命令
        :return: SQL 查询语句
        """

        sql = f"SELECT {columns} FROM {self.qt(table_name)}"

        if condition:
            sql += f" WHERE {condition}"

        if order_by:
            sql += f" ORDER BY {order_by}"

        if Page_Str:
            sql += f" {Page_Str}"

        return [sql]

    def record_search_dict(self, table_name, columns, condition_dict, order_by, Page_Str):
        sql = f"SELECT {columns} FROM {self.qt(table_name)}"

        # 如果有条件字典，则生成 WHERE 子句
        if condition_dict:
            # 使用字典的 key 来生成条件表达式（例如 column1 = %s AND column2 = %s）
            condition_list = [f"{self.qt(key)} = {self.ph()}" for key in condition_dict]
            condition_str = " AND ".join(condition_list)
            sql += f" WHERE {condition_str}"

        if order_by:
            sql += f" ORDER BY {order_by}"

        if Page_Str:
            sql += f" {Page_Str}"

        # 将字典中的值作为参数（按顺序）
        values = tuple(condition_dict.values())

        return [(sql, values)]

class DBClient:

    def __init__(self, mode, debug=False, jsonize=True, **kwargs):
        """
        mode: "ccport" / "sqlite" / "mysql"
        debug: 是否打印每条 sql_list 和 result
        kwargs: 不同模式下所需参数不同
          - 当 mode="ccport": 需要传入 client
              client = CCPC.Client(address='192.168.10.10:4403', key='None', debug=False)
              DB_target = SB.DBClient(mode='ccport', client=client, debug=False)
          - 当 mode="sqlite": 需要 db_path=None
          - 当 mode="mysql":  需要 host, user, password, database, etc.
        """
        self.sql_command = sql_command(mode)
        self.sql_execute = sql_execute(mode, debug=debug, jsonize=jsonize, **kwargs)
        self.debug = debug

    def send_to_server(self, sql_list, retries=5, delay=2):
        """
        sql_list 可以是文本（单条sql命令）；元组（sql命令+参数）；文本 或 元组 组成的列表(多条命令)
        """
        attempt = 0
        while attempt < retries:
            try:
                if self.debug:
                    print('-- DBClient -- send_to_server sql_list: ', sql_list)
                response = self.sql_execute.executor(sql_list)

                if response:
                    if self.debug:
                        print('-- DBClient -- send_to_server response: ', response)
                        print()
                    return response

                else:
                    print('DBClient 无 response 即将返回 None')
                    return None

            except Exception as e:
                attempt += 1
                print(f"与服务器通信时出错: {e}, 正在重试... 尝试 {attempt}/{retries}")
                if attempt < retries:
                    time.sleep(delay)  # 重试前等待一段时间

                else:
                    print("重试次数已达上限，放弃连接。")
                    return None

    def format_response_data(self, respond):
        ## 如果返回的格式不对 则返回 None
        if not isinstance(respond, list) or not isinstance(respond[0], dict):
            print('DBClient format_response_data error: 01')
            return

        if len(respond) == 0:
            print('DBClient format_response_data error: 02')
            return

        ## 如果返回的格式正确 则必为 List
        return_data = []
        for data in respond:
            if data.get('status') == 'success':
                return_data.append(data.get('data'))
            else:
                return_data.append(data)

        return return_data

    # -----------------------------------------------------
    def table_get(self):
        sql_list = self.sql_command.table_get()
        respond = self.send_to_server(sql_list)
        data_list = self.format_response_data(respond)

        # data_list 应符合预期
        if isinstance(data_list, list) and len(data_list) == 1 and isinstance(data_list[0], list):
            data_list = data_list[0]
        else:
            print(f"table_get 得到的 data_list 数据格式不对： {data_list}")
            return

        tab_list = [x.get('name') for x in data_list if x.get('name') != 'sqlite_sequence']

        return tab_list

    def table_add(self, table_name):
        if isinstance(table_name, str):
            table_name = [table_name]

        elif not isinstance(table_name, list):
            print(f"table_add 传入的 table_name 不对: {table_name}")
            return

        sql_list = self.sql_command.table_add(table_name)
        respond = self.send_to_server(sql_list)
        data_list = self.format_response_data(respond)

        return data_list

    def table_delete(self, table_name):
        if isinstance(table_name, str):
            table_name = [table_name]

        elif not isinstance(table_name, list):
            print(f"table_add 传入的 table_name 不对: {table_name}")
            return

        sql_list = self.sql_command.table_delete(table_name)
        respond = self.send_to_server(sql_list)
        data_list = self.format_response_data(respond)

        return data_list

    def table_rename(self, old_table_name, new_table_name):
        exist_tables = self.table_get()

        if old_table_name not in exist_tables:
            print('要更改的“表名”不在已有表名内！！')
            return

        sql_list = self.sql_command.table_rename(old_table_name, new_table_name)
        respond = self.send_to_server(sql_list)
        data_list = self.format_response_data(respond)

        return data_list

    def table_exists(self, table_name):
        """检查表是否存在"""
        return table_name in self.table_get()

    # -----------------------------------------------------
    def column_get(self, table_name: str):
        sql_list = self.sql_command.column_get(table_name)
        respond = self.send_to_server(sql_list)
        data_list = self.format_response_data(respond)

        # data_list 应符合预期
        if isinstance(data_list, list) and len(data_list) == 1 and isinstance(data_list[0], list):
            data_list = data_list[0]
        else:
            print(f"column_get 得到的 data_list 数据格式不对： {data_list}")
            return

        column_list = [x.get('name') for x in data_list]

        return column_list

    def column_add(self, table_name: str, columns: Union[str, list, dict]):
        """
        添加多个列。
        :param table_name: 表名 数据属性默认都是文本
        :param columns: 可以是文本（单个列定义）、列表（包含列定义的字符串或字典）、或者字典（列名和数据类型）。
        """
        if isinstance(columns, str):
            columns = {columns: "TEXT"}

        elif isinstance(columns, list):
            columns = {item: "TEXT" for item in columns}

        elif not isinstance(columns, dict):
            print(f"不支持的 column_dict_list 类型: {type(columns)}")
            return

        exist_columns = self.column_get(table_name)

        # 创建一个新的字典，仅包含需要添加的列及其数据类型
        want_add_columns_dict = {k: v for k, v in columns.items() if k not in exist_columns}

        if not want_add_columns_dict:
            print('需要添加的列都已存在!')
            return

        sql_list = self.sql_command.column_add(table_name, want_add_columns_dict)
        respond = self.send_to_server(sql_list)
        data_list = self.format_response_data(respond)

        return data_list

    def column_delete(self, table_name: str, columns: Union[str, list]):
        """
        添加多个列。
        :param table_name: 表名 数据属性默认都是文本
        :param columns: 可以是文本（单个列定义）、列表（包含列定义的字符串）
        """
        columns_exist_list = self.column_get(table_name)

        # 如果传入的是字符串，转换为列表处理
        if isinstance(columns, str):
            columns = [columns]

        columns = [x for x in columns if x != 'id']

        if not isinstance(columns, list) or not isinstance(columns_exist_list, list):
            print(f"del_col_list 和 columns_exist_list 必须是列表: {type(columns)} {type(columns_exist_list)}")
            return
        if not columns or not columns_exist_list:
            print(f"del_col_list 和 columns_exist_list 不能为空列表: {columns} {columns_exist_list}")
            return

        # 验证是否试图删除不存在的列
        non_existent_columns = [col for col in columns if col not in columns_exist_list]
        if non_existent_columns:
            print(f"不能删除不存在的列 {table_name}: {', '.join(non_existent_columns)}")
            return

        # 验证是否尝试删除所有列
        remaining_columns = [col for col in columns_exist_list if col not in columns]
        if not remaining_columns:
            print(f"不能删除所有列 {table_name}")
            return

        sql_list = self.sql_command.column_delete(table_name, columns, columns_exist_list)
        respond = self.send_to_server(sql_list)
        data_list = self.format_response_data(respond)

        return data_list

    def column_rename(self, table_name: str, old_column_name: str, new_column_name: str):
        exist_columns = self.column_get(table_name)

        if old_column_name not in exist_columns:
            print('要更改的列名不在已有列名内！！')
            return

        sql_list = self.sql_command.column_rename(table_name, old_column_name, new_column_name)
        respond = self.send_to_server(sql_list)
        data_list = self.format_response_data(respond)

        return data_list

    def column_exists(self, table_name: str, column: str):
        """检查列是否存在"""
        return column in self.column_get(table_name)

    # -----------------------------------------------------
    def get_columns_str(self, columns: Union[str, list]):
        if columns == '*':  # 通配符 默认值 不加引号
            pass

        elif isinstance(columns, list):  # 列表变字符串 加引号
            columns = ', '.join(self.sql_command.qt(col) for col in columns)

        elif isinstance(columns, str):  # 字符串 加引号
            columns = self.sql_command.qt(columns)

        else:
            raise ValueError(f"get_columns_str 传入的 columns 类型不可识别: {columns}")

        return columns

    def get_page_str(self, pagination):
        """
        处理分页参数，支持三种模式：
        1️⃣ 分页模式：("page", page_number, page_size) → LIMIT page_size OFFSET (page - 1) * page_size
        2️⃣ 切片模式：("slice", start, stop) → LIMIT (stop - start) OFFSET start
           - 支持 None，例如 ("slice", None, 20) 表示 OFFSET 0 LIMIT 20
        3️⃣ LIMIT 模式：("limit", limit, offset) → LIMIT limit OFFSET offset
        """
        if not isinstance(pagination, tuple) or len(pagination) != 3:
            return None

        mode, param1, param2 = pagination

        if mode == "page":
            page, page_size = param1, param2
            if page < 1 or page_size < 1:
                raise ValueError("page 和 page_size 必须大于 0")
            offset = (page - 1) * page_size
            return f"LIMIT {page_size} OFFSET {offset}"

        elif mode == "slice":
            start = param1 if param1 is not None else 0
            stop = param2
            if stop is not None:
                if stop <= start:
                    raise ValueError("stop 必须大于 start")
                limit = stop - start
                return f"LIMIT {limit} OFFSET {start}"
            else:
                return f"OFFSET {start}"  # stop=None 时只加 OFFSET

        elif mode == "limit":
            limit, offset = param1, param2
            if limit < 0 or offset < 0:
                raise ValueError("limit 和 offset 必须 >= 0")
            return f"LIMIT {limit} OFFSET {offset}"

        else:
            raise ValueError(f"不支持的分页模式: {mode}")

    # -----------------------------------------------------
    def record_add(self, table_name: str, data: dict):
        if not isinstance(data, dict) or not data:
            raise ValueError(f'record_add 输入的 data 不符合格式要求: {data}')

        sql_list = self.sql_command.record_add(table_name, data)
        respond = self.send_to_server(sql_list)
        data_list = self.format_response_data(respond)

        return data_list

    # 添加记录 自动生成表名 自动添加列名 自动判断是否有重复 no_dup=True 时 不允许添加重复数据
    def record_add_auto(self, table_name: str, data: dict, no_dup=True):
        if not isinstance(data, dict):
            raise ValueError(f'record_add_auto 输入的 data 不符合格式要求: {data}')

        # 添加表名字
        self.table_add(table_name)

        # 添加列名字
        col_list = list(data.keys())
        self.column_add(table_name, col_list)

        if no_dup:
            search_result = self.record_search(table_name, condition=data)

            if search_result is None:
                print('record_add_auto 搜索结果有问题')
                return
            elif len(search_result) == 0:  # 如果没有相同记录，插入数据
                print('record_add_auto 没有找到相同记录，即将插入数据')
                self.record_add(table_name, data)
            else:
                print(f"record_add_auto 找到相同记录: {data}")
                pass
        else:  # 允许重复 直接插入
            self.record_add(table_name, data)

    def record_update(self, table_name: str, data: dict, condition: Union[str, dict]):
        if not isinstance(data, dict) or not data:
            raise ValueError(f'record_add 输入的 data 不符合格式要求: {data}')

        if not condition:
            raise ValueError("record_update 传入的 condition 不能为 None")

        if isinstance(condition, dict):
            sql_list = self.sql_command.record_update_dict(table_name, data, condition)
        else:
            sql_list = self.sql_command.record_update(table_name, data, condition)
        respond = self.send_to_server(sql_list)
        data_list = self.format_response_data(respond)

        return data_list

    def record_delete(self, table_name: str, condition: str):

        if isinstance(condition, dict):
            sql_list = self.sql_command.record_delete_dict(table_name, condition)
        else:
            sql_list = self.sql_command.record_delete(table_name, condition)
        respond = self.send_to_server(sql_list)
        data_list = self.format_response_data(respond)

        return data_list

    def record_search(self, table_name: str, condition: Union[str, dict] = None,
                      columns: Union[str, list] = '*', order_by: str = None,
                      pagination: tuple = None, include_id=True):
        """
        查询记录，支持三种分页模式：
        1️⃣ 分页模式：("page", page_number, page_size)
           - record_search("users", pagination=("page", 2, 10))  # 第 2 页，每页 10 条
        2️⃣ 切片模式：("slice", start, stop)
           - record_search("orders", pagination=("slice", 10, 20))  # 获取第 10 到 19 条记录
           - 支持 None，例如 ("slice", None, 20) 表示 OFFSET 0 LIMIT 20
        3️⃣ LIMIT 模式：("limit", limit, offset)
           - record_search("products", pagination=("limit", 5, 15))  # LIMIT 5 OFFSET 15
        """
        columns = self.get_columns_str(columns)

        # 处理分页参数
        page_str = self.get_page_str(pagination)

        # 构建 SQL 查询
        if isinstance(condition, dict):
            sql_list = self.sql_command.record_search_dict(table_name, columns, condition, order_by, page_str)
        else:  # 可接受 None
            sql_list = self.sql_command.record_search(table_name, columns, condition, order_by, page_str)
        respond = self.send_to_server(sql_list)
        data_list = self.format_response_data(respond)

        # 数据格式校验
        if isinstance(data_list, list) and len(data_list) == 1 and isinstance(data_list[0], list):
            data_list = data_list[0]
            if not include_id:
                return [{k: v for k, v in one.items() if k != 'id'} for one in data_list]
            return data_list
        else:
            print(f"record_search 得到的 data_list 数据格式不对： {data_list}")
            return None

    def record_count(self, table_name: str, ):
        count = self.record_search(table_name=table_name, columns='COUNT(*)')
        return len(count)

    # -----------------------------------------------------
    def renew(self):
        """删除数据库中的所有表"""
        tables = self.table_get()  # 获取所有表
        for table in tables:
            self.table_delete(table)  # 删除表
        print("---- 所有表格已删除。数据库已重置。")

    def print_all(self):
        """打印数据库中所有表的列名和数据"""
        tables = self.table_get()  # 获取所有表

        print(f"------------------------------------------------------------")
        for table_name in tables:
            print('++++++++++++++++++++++++++++++')
            print(f"---- 表名: {table_name}")
            print(f"---- 列名: {self.column_get(table_name)}")
            print(f"---- 表内数据: ")
            rows = self.record_search(table_name, include_id=True)  # 获取所有记录
            for row in rows:
                print("----", row)  # 打印每一行数据
            print('++++++++++++++++++++++++++++++')
        print(f"------------------------------------------------------------")

    def web_start(self, host="127.0.0.1", port=7589):
        """
        启动一个简单的Web服务器，在浏览器中查看数据库表和数据。
        """
        web_server = StartWebServer(self, host=host, port=port)
        web_server.start()

    def command(self, command):
        """执行自定义命令"""
        respond = self.send_to_server(command)
        data_list = self.format_response_data(respond)

        return data_list

# -----------------------------------------------------
class sql_execute:

    def __init__(self, mode, debug=False, jsonize=True, **kwargs):
        """
        mode: "ccport" / "sqlite" / "mysql"
        kwargs: 不同模式下所需参数不同
          - 当 mode="ccport": 需要 传入client
          - 当 mode="sqlite": 需要 传入connection
          - 当 mode="mysql":  需要 传入connection
        """
        self.mode = mode
        self.executor = None  # 将被赋值为一个可调用对象
        self.connection = None
        self.debug = debug
        self.jsonize = jsonize
        if self.mode == "ccport":
            self.executor = self._init_ccport(**kwargs)
        elif self.mode == "sqlite" or self.mode == "mysql":
            self.executor = self._init_connect(**kwargs)
        else:
            raise ValueError(f"Unsupported mode: {self.mode}")

    def _init_ccport(self, client):
        # client = CCPC.Client(address=address, protocol=protocol,
        # timeout=timeout, debug=self.debug, jsonize=self.jsonize, key=key)
        def executor(sql_list):
            return client.send_and_receive(sql_list)
        return executor

    def _init_connect(self, connection):
        self.connection = connection
        def executor(sql_list):
            return Execute_sql_list(self.connection, sql_list, debug=self.debug, jsonize=self.jsonize)
        return executor

    def execute_sql_list(self, sql_list):
        return self.executor(sql_list)

    def close(self):
        if self.mode in ["sqlite", "mysql"] and self.connection:
            self.connection.close()

def Execute_sql_list(connection, sql_list, debug=False, jsonize=True):
    """
    :param connection: 数据库连接对象
    :param sql_list:
            可传入文本，单条 SQL 语句
            可传入元组，(sql, params) 的形式
            可传入列表，每个元素必须是符合上述条件的 文本 或者 元组
    :return: 返回一个包含所有 SQL 结果的列表，列表中每个结果是一个字典。如果出现错误，返回一个包含错误信息的字典。
    """
    # 标记事务是否已经启动
    transaction_started = False

    try:
        cursor = connection.cursor()
        result_list = []  # 用于存储多条 SQL 的结果

        # 传入的参数如果是 文本 或 元组（文本+列表），则外面套一层List
        if not isinstance(sql_list, list):
            sql_list = [sql_list]

        # 执行每条命令
        for item in sql_list:
            # 拆解出 sql 和 params
            if isinstance(item, tuple):
                sql, params = item
            else:
                sql = item
                params = ()

            # 如果 jsonize=True，需要对每个元素做 JSON 化操作
            if jsonize:
                new_params = []
                for p in params:
                    new_params.append(_jsonize_param(p))
                params = tuple(new_params)

            if debug:
                print(f'---- Execute one_sql_list: ', item)

            sql_lower = sql.strip().lower()

            # 如果是查询语句（SELECT、PRAGMA、SHOW 等）
            if sql_lower.startswith(("select", "pragma", "show")):

                cursor.execute(sql, params)

                column_names = [description[0] for description in cursor.description]

                rows = cursor.fetchall()

                # 读取时，进行去标签 + 反序列化
                result_rows = []
                for row in rows:
                    row_dict = {}
                    for col_name, val in zip(column_names, row):
                        if jsonize:
                            row_dict[col_name] = _dejsonize_value(val)
                        else:
                            row_dict[col_name] = val
                    result_rows.append(row_dict)

                result_list.append({"status": "success", "data": result_rows})

            else:
                # 启动事务，只在非查询操作时进行 写入操作
                if not transaction_started:
                    cursor.execute("BEGIN TRANSACTION")
                    transaction_started = True

                # 非查询语句，执行后记录状态
                cursor.execute(sql, params)
                result_list.append({"status": "success", "data": {"rows_affected": cursor.rowcount}})

        # 如果有非查询操作，提交事务
        if transaction_started:
            connection.commit()

        if debug:
            print('---- Execute result_list: ', result_list)

        return result_list

    except Exception as e:
        if transaction_started:
            connection.rollback()  # 如果出现错误，回滚事务
        return [{"status": "failed", "message": f"Execute_sql_list 执行报错：{str(e)}"}]  # 统一返回一个包含错误信息的列表

# -----------------------------------------------------
import http.server
import socketserver
import urllib.parse
import html
import traceback

class StartWebServer:
    def __init__(self, dbclient, host="127.0.0.1", port=5000):
        self.dbclient = dbclient
        self.host = host
        self.port = port

    def start(self):
        class CustomTCPServer(socketserver.TCPServer):
            allow_reuse_address = True

        handler = self.RequestHandler
        handler.dbclient = self.dbclient

        with CustomTCPServer((self.host, self.port), handler) as httpd:
            print(f"Web server is running at http://{self.host}:{self.port}")
            try:
                httpd.serve_forever()
            except KeyboardInterrupt:
                print("服务器已停止。")

    class RequestHandler(http.server.BaseHTTPRequestHandler):
        def do_GET(self):
            try:
                parsed_path = urllib.parse.urlparse(self.path)
                table_name = parsed_path.path.strip("/")

                if not table_name:
                    self.handle_table_page("")
                else:
                    self.handle_table_page(urllib.parse.unquote(table_name))
            except Exception:
                traceback.print_exc()
                self.send_error(500, "Internal Server Error")

        def handle_table_page(self, table_name):
            """ 处理首页和表数据页面 """
            if not table_name:
                content = "<h2>请选择左侧的表来查看数据</h2>"
            else:
                all_tables = self.dbclient.table_get()
                if table_name not in all_tables:
                    self.send_error(404, f"Table '{html.escape(table_name)}' not found.")
                    return

                columns = self.dbclient.column_get(table_name)

                if 'id' in columns:
                    order_by = 'id DESC'
                else:
                    order_by = None

                records = self.dbclient.record_search(table_name, include_id=True, order_by=order_by)

                content = self.generate_table_html(table_name, columns, records)

            self.send_html_response(self.generate_base_page(content, f"表名: {table_name}" if table_name else "DBClient Viewer"))

        def send_html_response(self, html_content):
            """ 统一 HTML 响应 """
            self.send_response(200)
            self.send_header("Content-type", "text/html; charset=utf-8")
            self.end_headers()
            self.wfile.write(html_content.encode())

        def generate_base_page(self, content_html, title="DBClient Viewer"):
            """ 页面模板 """
            return f"""<!DOCTYPE html>
            <html><head>
                <meta charset="utf-8"/>
                <title>{title}</title>
                <style>
                    body {{ font-family: Arial, sans-serif; margin: 0; padding: 0; }}
                    .container {{ display: flex; height: 100vh; }}
                    .sidebar {{ width: 200px; background: #f0f0f0; padding: 20px; overflow-y: auto; border-right: 1px solid #ccc; }}
                    .content {{ flex: 1; padding: 20px; overflow-y: auto; }}
                    a {{ text-decoration: none; color: #333; font-size: 1.2em; display: block; padding: 5px 0; }}
                    a:hover {{ text-decoration: underline; }}
                    h2 {{ font-size: 2em; margin-bottom: 20px; }}
                    table {{ border-collapse: collapse; width: 100%; }}
                    th, td {{ border: 1px solid #ccc; padding: 8px; text-align: left; }}
                    th {{ background: #e0e0e0; }}
                    tr:nth-child(even) {{ background: #f9f9f9; }}
                </style>
            </head>
            <body>
                <div class="container">
                    <div class="sidebar">
                        <h3>数据表</h3>
                        {self.generate_table_links()}
                    </div>
                    <div class="content">{content_html}</div>
                </div>
            </body></html>"""

        def generate_table_links(self):
            """ 生成侧边栏的表列表 """
            return "".join(f'<p><a href="/{urllib.parse.quote(t)}">{html.escape(t)}</a></p>' for t in self.dbclient.table_get())

        def generate_table_html(self, table_name, columns, records):
            """ 生成表格 HTML """
            thead = "".join(f"<th>{html.escape(col)}</th>" for col in columns)
            tbody = "".join(
                "<tr>" + "".join(f"<td>{html.escape(str(record.get(col, '')))}</td>" for col in columns) + "</tr>"
                for record in records
            ) or f'<tr><td colspan="{len(columns)}" style="text-align:center;">暂无数据</td></tr>'

            return f"""<h2>表名: {html.escape(table_name)}</h2>
            <div class="table-container">
                <table><thead><tr>{thead}</tr></thead>
                <tbody>{tbody}</tbody></table>
            </div>"""

# -----------------------------------------------------
if __name__ == '__main__':

    import CCPortComm_v5_1 as CCPC
    import sqlite3  # 根据需要选择合适的数据库连接方式

    # file = r'\\192.168.10.63\00_Data\20_DBs\03Contract_finance.db'

    # file = r'\\192.168.10.63\00_Data\20_DBs\03Contract_finance.db'
    #
    # file = r'D:\00_MyPython\00_Project_AI\12_Salary_System\salary.db'

    # file = r'\\192.168.10.63\zzPython\00_Data\20_DBs\02DingTalk_Finance.db'

    file = r'\\192.168.10.63\zzPython\00_Data\20_DBs\02DingTalk_Finance.db'

    file = r'\\192.168.10.63\zzPython\00_Data\22_DB_Clean\02DingTalk_clean.db'

    file = r'\\192.168.10.26\00_Company_OA_250215\12_Salary_System\salary.db'



    # file = r'\\192.168.10.63\00_Data\22_DB_Clean\01DataLink_clean.db'
    DB = DBClient(mode='sqlite', connection=sqlite3.connect(file), debug=False)



    DB.web_start()

    # client = CCPC.Client(address='192.168.10.10:4401', key='vQVfpCgvb0hSsKzd', debug=False)
    # DB = DBClient(mode='ccport', client=client, debug=False)

    # DB.record_update('User_Data_v2', {'confirmed': 'TRUE'}, condition='username = "18637199120"')

    # results = DB.record_search('User_Data_v2')
    #
    # for one in results:
    #     roles = one['roles']
    #
    #     if one['real_name'] in ['程畅', '孙菲', '孙冰']:
    #         roles = ['user', '管理员']
    #
    #     if one['real_name'] in ['王京辉', '张莹', '汤振兴']:
    #         roles.append('项目经理')
    #
    #     roles = sorted(list(set(roles)))
    #
    #     DB.record_update('User_Data_v2', {'roles': roles}, f'username = {one["username"]}')
    #



# 示例一  连接本地文件：
# import sqlite3
# import SqlBase_v3_15 as SB
# file = r'T:\10_BankMessage_01Phone\00CashFlow.db'
# bank_db = SB.DBClient(mode='sqlite', connection=sqlite3.connect(file), debug=False)


# 示例二  连接自定义的CCPC：
# import SqlBase_v3_15 as SB
# import CCPortComm_v5_1 as CCPC
# client = CCPC.Client(address='192.168.10.20:4500', key='my_key', debug=False)
# bank_db = SB.DBClient(mode='ccport', client=client, debug=False)


# 示例三  连接mysql (还未调试过估计 bug 较多)：
# import SqlBase_v3_15 as SB
# bank_db = SB.DBClient(mode='mysql', host=？, user=？, password？, databas？e, etc？, debug=False)